package com.szboanda.parser;

import com.szboanda.parser.impl.HasFieldTagParserImpl;
import com.szboanda.parser.impl.SqlBindingParamParserImpl;
import com.szboanda.parser.impl.ValueReplaceParamParserImpl;
import org.apache.commons.lang.StringUtils;

import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author ：zhangsheng
 * @date ：Created in 2020/2/22 9:25
 * @description：sql解析器
 * @modified By：
 * @version: 1.0$
 */
public class SqlScriptParserUtil {
    /**
     * 单例
     */
    private static volatile SqlScriptParserUtil ourInstance;

    /**
     * 排序字段匹配
     */
    private static final Pattern ORDER_PATTERN  = Pattern.compile("(ORDER|order)\\s*(BY|by)[^\\)]+$");

    /**
     * 排他数据库类型匹配
     */
    private static final Pattern DB_OTHER_PATTERN = Pattern.compile("<OtherDB>(?<otherSql>[\\s\\S]*?)</OtherDB>");

    /**
     * 通用数据库类型匹配
     */
    private static final Pattern DB_COMMON_PATTERN = Pattern.compile("<(?<types>(Mysql|Oracle|SqlServer|\\|)+)>(?<commonSql>[\\s\\S]*?)</(Mysql|Oracle|SqlServer|\\|)+>");

    /**
     * 所有数据库类型匹配
     */
    private static final Pattern DB_ALL_PATTERN = Pattern.compile(String.format("\\s*?((?<common>%s)|(?<other>%s))+\\s*?", DB_COMMON_PATTERN.pattern(), DB_OTHER_PATTERN.pattern()));

    /**
     * 获取实例
     * @return
     */
    public static SqlScriptParserUtil getInstance() {
        if (null == ourInstance) {
            synchronized (SqlScriptParserUtil.class) {
                if (null == ourInstance) {
                    ourInstance = new SqlScriptParserUtil();
                }
            }
        }
        return ourInstance;
    }

    /**
     * 构造私有化
     */
    private SqlScriptParserUtil() {
    }

    /**
     * 获取带占位符的sql脚本，并初始化jdbc参数
     * @param sqlScript 数据集原始脚本
     * @param sqlArgs  jdbc参数存储
     * @param sqlContext 脚本解析上下文
     * @return
     * @throws SqlParserException
     */
    public String getBindingSql(String sqlScript, List<Object> sqlArgs, SqlContext sqlContext) throws SqlParserException {
        //数据库类型选择
        String targetSql = this.chooseByDatabaseType(sqlScript, sqlContext.getDataBaseType());
        Map<String, ?> context = sqlContext.getParams();
        // 标签解析
        ISqlParser hasFieldTagParser = new HasFieldTagParserImpl();
        targetSql = hasFieldTagParser.parse(targetSql, sqlArgs, context);
        //值替换
        ISqlParser valueReplaceParamParser = new ValueReplaceParamParserImpl();
        targetSql = valueReplaceParamParser.parse(targetSql, sqlArgs, context);
        // 标签解析
        targetSql = hasFieldTagParser.parse(targetSql, sqlArgs, context);
        //变量绑定
        ISqlParser sqlBindingParamParser = new SqlBindingParamParserImpl();
        targetSql = sqlBindingParamParser.parse(targetSql, sqlArgs, context);

        return targetSql;
    }

    /**
     * 根据数据库类型选择sql语句
     * @param sqlScript
     * @param dataBaseType
     * @return
     */
    public String chooseByDatabaseType(String sqlScript, DataBaseType dataBaseType) {
        /*
        ^\s*?((?<common><(?<types>(Mysql|Oracle|SqlServer|\|)+)>(?<commonSql>[\s\S]*?)</(Mysql|Oracle|SqlServer|\|)+>)
        |(?<other><OtherDB>(?<otherSql>[\s\S]*?)</OtherDB>))+\s*?$
         */
        Matcher match = DB_ALL_PATTERN.matcher(sqlScript);
        if (match.find()) {
            do {
                String commonSql = match.group("common");
                if (StringUtils.isNotEmpty(commonSql)) {
                    String types = match.group("types");
                    if (StringUtils.isNotEmpty(types) && types.matches(String.format("\\S*?%s\\S*?", dataBaseType.name()))) {
                        return match.group("commonSql");
                    }
                }
                String otherSql = match.group("other");
                if (StringUtils.isNotEmpty(otherSql)) {
                    return match.group("otherSql");
                }
            } while (match.find());
            return sqlScript;
        } else {
            return sqlScript;
        }
    }

    /**
     *  获取带有查询上限的sql
     * @param sql
     * @param queryLimitSize
     * @param dataBaseType
     * @return
     * @throws SqlParserException
     */
    public String getQueryLimitSql(String sql, int queryLimitSize, DataBaseType dataBaseType) throws SqlParserException {
        if (dataBaseType.equals(DataBaseType.SqlServer)) {
            String newsql = sql.trim();
            String order = this.getSqlServerOrder(newsql);
            if (StringUtils.isEmpty(order)) {
                return sql;
            } else {
                return this.getPageSql(sql, 1, queryLimitSize, dataBaseType);
            }
        } else {
            return this.getPageSql(sql, 1, queryLimitSize, dataBaseType);
        }
    }


    /**
     * 根据数据源类型获取的分页查询语句
     * @param sql
     * @param page
     * @param pageSize
     * @param dataBaseType
     * @return
     * @throws SqlParserException
     */
    public String getPageSql(String sql, int page, int pageSize, DataBaseType dataBaseType) throws SqlParserException {
        int start = (page - 1) * pageSize;
        int end = page * pageSize;
        StringBuilder sbr = new StringBuilder();
        // 去掉换行符
        sql = sql.replaceAll("\n","");
        switch (dataBaseType) {
            case Mysql:
                sbr.append(sql);
                sbr.append(" LIMIT ");
                sbr.append(start);
                sbr.append(", ");
                sbr.append(pageSize);
                break;
            case Oracle:
                // 修改oracle数据库语句SQL的封装
                sbr.append("SELECT * FROM ( SELECT TABLE_ALIAS.*, ROWNUM RN FROM (");
                sbr.append(sql).append(") TABLE_ALIAS");
                sbr.append(" WHERE ROWNUM <=").append(end).append(')');
                sbr.append(" WHERE RN >").append(start);
                break;
            case SqlServer:
                // sql server 或者其他类型数据库，不做分页
                String newsql = sql.trim();
                String order = this.getSqlServerOrder(newsql);
                if (StringUtils.isEmpty(order)) {
                    throw new SqlParserException("sqlserver 脚本["+sql + "] 缺少 order by ");
                }
                //去掉后缀
                newsql = newsql.replaceFirst(ORDER_PATTERN.pattern(), "");
                if (StringUtils.isNotBlank(order)) {
                    newsql = newsql.replaceFirst("^SELECT",
                            String.format("SELECT row_number () OVER (%s) RN,", order));
                    newsql = newsql.replaceFirst("^select",
                            String.format("SELECT row_number () OVER (%s) RN,", order));
                    newsql = String.format("SELECT TOP %d * FROM ( %s ) A WHERE A.RN > %d", pageSize, newsql, start);
                    sbr.append(newsql);
                } else {
                    sbr.append(sql);
                }
                break;
            default:
                sbr.append(sql);
        }
        return sbr.toString();
    }


    /**
     *
     * @param sql
     * @return
     * @throws SqlParserException
     */
    public String getCountSql(String sql, DataBaseType dataBaseType) throws SqlParserException{
        sql = sql.replaceFirst(ORDER_PATTERN.pattern(), "");
        sql = String.format("SELECT COUNT(1) COU FROM(\n %s) COUNT_TABLE", sql);
        return sql;
    }
    /**
     * 获取排序语句
     *
     * @param querySql
     * @return
     */
    private String getSqlServerOrder(String querySql) {
        Matcher matcher = ORDER_PATTERN.matcher(querySql);
        if (matcher.find()) {
            return matcher.group();
        }
        return null;
    }



}
